
--------------------------------------------------------
--  DDL for Sequence S_AGENDA
--------------------------------------------------------

   CREATE SEQUENCE   "S_AGENDA"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_CONTRAT
--------------------------------------------------------

   CREATE SEQUENCE   "S_CONTRAT"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_EC
--------------------------------------------------------

   CREATE SEQUENCE   "S_EC"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_FORMATION
--------------------------------------------------------

   CREATE SEQUENCE   "S_FORMATION"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_INTERVENANT
--------------------------------------------------------

   CREATE SEQUENCE   "S_INTERVENANT"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_JOUR
--------------------------------------------------------

   CREATE SEQUENCE   "S_JOUR"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_PROMOTION
--------------------------------------------------------

   CREATE SEQUENCE   "S_PROMOTION"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_SALLE
--------------------------------------------------------

   CREATE SEQUENCE   "S_SALLE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_SEMESTRE
--------------------------------------------------------

   CREATE SEQUENCE   "S_SEMESTRE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_SESSION
--------------------------------------------------------

   CREATE SEQUENCE   "S_SESSION"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_STATUS
--------------------------------------------------------

   CREATE SEQUENCE   "S_STATUS"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/
--------------------------------------------------------
--  DDL for Sequence S_UE
--------------------------------------------------------

   CREATE SEQUENCE   "S_UE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE
/














--------------------------------------------------------
--  DDL for Table CONTRAT
--------------------------------------------------------

  CREATE TABLE  "CONTRAT" ("IDC" NUMBER, "ANNEEEX" NUMBER)
/

--------------------------------------------------------
--  DDL for Table EC
--------------------------------------------------------

  CREATE TABLE  "EC" ("IDEC" NUMBER(10,0), "NOM" VARCHAR2(50 BYTE), "CODE" VARCHAR2(20 BYTE))
/


--------------------------------------------------------
--  DDL for Table FORMATION
--------------------------------------------------------

  CREATE TABLE  "FORMATION" ("IDF" NUMBER, "GRADE" VARCHAR2(20 BYTE), "NIVEAU" VARCHAR2(20 BYTE), "FILIERE" VARCHAR2(80 BYTE), "PARCOURS" VARCHAR2(40 BYTE), "TYPEE" VARCHAR2(30 BYTE), "IDCF" NUMBER)
/











--------------------------------------------------------
--  DDL for Table SEMESTRE
--------------------------------------------------------

  CREATE TABLE  "SEMESTRE" ("IDSEM" NUMBER, "IDF" NUMBER, "NPERIODE" NUMBER)
/
--------------------------------------------------------
--  DDL for Table UE
--------------------------------------------------------

  CREATE TABLE  "UE" ("IDUE" NUMBER(10,0), "IDF" NUMBER, "NOM" VARCHAR2(50 BYTE), "IDSEM" NUMBER)
/
--------------------------------------------------------
--  DDL for Table EC_UE
--------------------------------------------------------

  CREATE TABLE  "EC_UE" ("IDF" NUMBER, "IDEC" NUMBER, "IDUE" NUMBER, "NBRCM" NUMBER, "NBRTD" NUMBER, "COEFF" NUMBER)
/
--------------------------------------------------------
--  DDL for Table STATUT
--------------------------------------------------------

  CREATE TABLE  "STATUT" ("IDSTAT" NUMBER(10,0), "NOM" VARCHAR2(40 BYTE), "NBRH" NUMBER, "NBRHSUP" NUMBER)
/
--------------------------------------------------------
--  DDL for Table INTERVENANT
--------------------------------------------------------

  CREATE TABLE  "INTERVENANT" ("IDI" NUMBER(10,0), "NOM" VARCHAR2(40 BYTE), "PRENOM" VARCHAR2(40 BYTE), "MAIL" VARCHAR2(60 BYTE), "PASSWD" VARCHAR2(50 BYTE), "TEL" VARCHAR2(30 BYTE), "IDSTAT" NUMBER, "ENSERVICE" VARCHAR2(4 BYTE))
/
--------------------------------------------------------
--  DDL for Table PROMOTION
--------------------------------------------------------

  CREATE TABLE  "PROMOTION" ("IDP" NUMBER(10,0), "IDF" NUMBER(10,0), "ANNEE" NUMBER, "NBRGRP" NUMBER)
/
--------------------------------------------------------
--  DDL for Table INTERV_EC
--------------------------------------------------------

  CREATE TABLE  "INTERV_EC" ("IDI" NUMBER, "IDEC" NUMBER)
/
--------------------------------------------------------
--  DDL for Table JOUR
--------------------------------------------------------

  CREATE TABLE  "JOUR" ("IDJ" NUMBER, "DATEE" DATE, "FERIEE" VARCHAR2(4 BYTE))
/
--------------------------------------------------------
--  DDL for Table PERIODE
--------------------------------------------------------

  CREATE TABLE  "PERIODE" ("IDP" NUMBER, "IDSEM" NUMBER, "DEBUT" DATE, "FIN" DATE)
/

--------------------------------------------------------
--  DDL for Table SALLE
--------------------------------------------------------

  CREATE TABLE  "SALLE" ("IDS" NUMBER, "NUM" NUMBER, "NOM" VARCHAR2(50 BYTE), "BATIMENT" VARCHAR2(50 BYTE), "TYPEE" VARCHAR2(50 BYTE))
/

--------------------------------------------------------
--  DDL for Table FICHEVOEUX
--------------------------------------------------------

  CREATE TABLE  "FICHEVOEUX" ("IDI" NUMBER, "PERIODE" VARCHAR2(20 BYTE), "ANNEE" NUMBER, "IDJOUR" NUMBER, "NCRENEAU" NUMBER, "POIDS" NUMBER, "VALIDE" VARCHAR2(20 BYTE), "PRIORITE" NUMBER(*,0))
/

--------------------------------------------------------
--  DDL for Table AGENDA
--------------------------------------------------------

  CREATE TABLE  "AGENDA" ("IDCR" NUMBER, "IDP" NUMBER, "IDS" NUMBER, "IDEC" NUMBER, "IDI" NUMBER, "IDJ" NUMBER, "DEBUT" NUMBER, "FIN" NUMBER, "NUMG" NUMBER, "TYPEE" VARCHAR2(20 BYTE), "VALIDE" VARCHAR2(4 BYTE), "VISIBLE" VARCHAR2(4 BYTE))
/
--------------------------------------------------------
--  DDL for Table SESSIONS
--------------------------------------------------------

  CREATE TABLE  "SESSIONS" ("IDI" NUMBER, "KEYS" VARCHAR2(50 BYTE), "EXPIRE" TIMESTAMP (6))
/





--------------------------------------------------------
--  DDL for Index CONTRAT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX  "CONTRAT_PK" ON  "CONTRAT" ("IDC")
/
--------------------------------------------------------
--  DDL for Index FORMATION_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX  "FORMATION_PK" ON  "FORMATION" ("IDF")
/
--------------------------------------------------------
--  DDL for Index FORMATION_UK1
--------------------------------------------------------

  CREATE UNIQUE INDEX  "FORMATION_UK1" ON  "FORMATION" ("IDCF", "GRADE", "NIVEAU", "FILIERE", "PARCOURS", "TYPEE")
/
--------------------------------------------------------
--  DDL for Index PERIODE_UK1
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PERIODE_UK1" ON  "PERIODE" ("IDP", "IDSEM")
/
--------------------------------------------------------
--  DDL for Index PK_IDCR
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDCR" ON  "AGENDA" ("IDCR")
/
--------------------------------------------------------
--  DDL for Index PK_IDEC
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDEC" ON  "EC" ("IDEC")
/
--------------------------------------------------------
--  DDL for Index PK_IDI
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDI" ON  "INTERVENANT" ("IDI")
/
--------------------------------------------------------
--  DDL for Index PK_IDJ
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDJ" ON  "JOUR" ("IDJ")
/
--------------------------------------------------------
--  DDL for Index PK_IDP
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDP" ON  "PROMOTION" ("IDP")
/
--------------------------------------------------------
--  DDL for Index PK_IDS
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDS" ON  "SALLE" ("IDS")
/
--------------------------------------------------------
--  DDL for Index SALLE NOM
--------------------------------------------------------

  CREATE UNIQUE INDEX  "I_NOM" ON  "SALLE" ("NOM")
/

--------------------------------------------------------
--  DDL for Index PK_IDSEM
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDSEM" ON  "SEMESTRE" ("IDSEM")
/
--------------------------------------------------------
--  DDL for Index PK_IDSTAT
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_IDSTAT" ON  "STATUT" ("IDSTAT")
/
--------------------------------------------------------
--  DDL for Index PK_UE
--------------------------------------------------------

  CREATE UNIQUE INDEX  "PK_UE" ON  "UE" ("IDUE")
/
--------------------------------------------------------
--  DDL for Index SESSIONS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX  "SESSIONS_PK" ON  "SESSIONS" ("KEYS")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_AG_IDI
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_AG_IDI" ON  "AGENDA" ("IDI", "IDJ", "DEBUT", "FIN")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_AG_IDS
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_AG_IDS" ON  "AGENDA" ("IDS", "IDJ", "DEBUT", "FIN")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_EC
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_EC" ON  "EC" ("NOM")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_EC_CODE
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_EC_CODE" ON  "EC" ("CODE")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_EC_FORMATION
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_EC_FORMATION" ON  "EC_UE" ("IDEC", "IDF")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_IDI
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_IDI" ON  "INTERVENANT" ("NOM", "PRENOM", "TEL")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_IDI_IDEC
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_IDI_IDEC" ON  "INTERV_EC" ("IDI", "IDEC")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_IDI_VOEUX
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_IDI_VOEUX" ON  "FICHEVOEUX" ("IDI", "PERIODE", "ANNEE", "IDJOUR")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_JOUR_DATE
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_JOUR_DATE" ON  "JOUR" ("DATEE")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_PROM_FOR_ANNEE
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_PROM_FOR_ANNEE" ON  "PROMOTION" ("IDF", "ANNEE")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_SEM_IDF
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_SEM_IDF" ON  "SEMESTRE" ("IDF", "NPERIODE")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_STAT_NOM
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_STAT_NOM" ON  "STATUT" ("NOM")
/
--------------------------------------------------------
--  DDL for Index UNIQUE_UE
--------------------------------------------------------

  CREATE UNIQUE INDEX  "UNIQUE_UE" ON  "UE" ("IDF", "NOM")
/



--------------------------------------------------------
--  Constraints for Table AGENDA
--------------------------------------------------------

  ALTER TABLE  "AGENDA" ADD CONSTRAINT "UNIQUE_AG_IDI" UNIQUE ("IDI", "IDJ", "DEBUT", "FIN") ENABLE;
  ALTER TABLE  "AGENDA" ADD CONSTRAINT "UNIQUE_AG_IDS" UNIQUE ("IDS", "IDJ", "DEBUT", "FIN") ENABLE;
  ALTER TABLE  "AGENDA" ADD CONSTRAINT "PK_IDCR" PRIMARY KEY ("IDCR") ENABLE;
  ALTER TABLE  "AGENDA" ADD CHECK (visible in ('true','fals')) ENABLE;
  ALTER TABLE  "AGENDA" ADD CHECK (valide in ('true','fals')) ENABLE;
  ALTER TABLE  "AGENDA" MODIFY ("VISIBLE" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("VALIDE" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("TYPEE" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("NUMG" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("FIN" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("DEBUT" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("IDJ" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("IDI" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("IDEC" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("IDS" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("IDP" NOT NULL ENABLE);
  ALTER TABLE  "AGENDA" MODIFY ("IDCR" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table CONTRAT
--------------------------------------------------------

  ALTER TABLE  "CONTRAT" ADD CONSTRAINT "CONTRAT_PK" PRIMARY KEY ("IDC") ENABLE;
  ALTER TABLE  "CONTRAT" MODIFY ("ANNEEEX" NOT NULL ENABLE);
  ALTER TABLE  "CONTRAT" MODIFY ("IDC" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EC
--------------------------------------------------------

  ALTER TABLE  "EC" ADD CONSTRAINT "UNIQUE_EC_CODE" UNIQUE ("CODE") ENABLE;
  ALTER TABLE  "EC" MODIFY ("CODE" NOT NULL ENABLE);
  ALTER TABLE  "EC" ADD CONSTRAINT "UNIQUE_EC" UNIQUE ("NOM") ENABLE;
  ALTER TABLE  "EC" ADD CONSTRAINT "PK_IDEC" PRIMARY KEY ("IDEC") ENABLE;
  ALTER TABLE  "EC" MODIFY ("NOM" NOT NULL ENABLE);
  ALTER TABLE  "EC" MODIFY ("IDEC" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EC_UE
--------------------------------------------------------

  ALTER TABLE  "EC_UE" ADD CONSTRAINT "UNIQUE_EC_FORMATION" UNIQUE ("IDEC", "IDF") ENABLE;
  ALTER TABLE  "EC_UE" MODIFY ("COEFF" NOT NULL ENABLE);
  ALTER TABLE  "EC_UE" MODIFY ("NBRTD" NOT NULL ENABLE);
  ALTER TABLE  "EC_UE" MODIFY ("NBRCM" NOT NULL ENABLE);
  ALTER TABLE  "EC_UE" MODIFY ("IDUE" NOT NULL ENABLE);
  ALTER TABLE  "EC_UE" MODIFY ("IDEC" NOT NULL ENABLE);
  ALTER TABLE  "EC_UE" MODIFY ("IDF" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table FICHEVOEUX
--------------------------------------------------------

  ALTER TABLE  "FICHEVOEUX" ADD CONSTRAINT "UNIQUE_IDI_VOEUX" UNIQUE ("IDI", "PERIODE", "ANNEE", "IDJOUR") ENABLE;
  ALTER TABLE  "FICHEVOEUX" MODIFY ("POIDS" NOT NULL ENABLE);
  ALTER TABLE  "FICHEVOEUX" MODIFY ("NCRENEAU" NOT NULL ENABLE);
  ALTER TABLE  "FICHEVOEUX" MODIFY ("IDJOUR" NOT NULL ENABLE);
  ALTER TABLE  "FICHEVOEUX" MODIFY ("ANNEE" NOT NULL ENABLE);
  ALTER TABLE  "FICHEVOEUX" MODIFY ("PERIODE" NOT NULL ENABLE);
  ALTER TABLE  "FICHEVOEUX" MODIFY ("IDI" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table FORMATION
--------------------------------------------------------

  ALTER TABLE  "FORMATION" ADD CONSTRAINT "FORMATION_UK1" UNIQUE ("IDCF", "GRADE", "NIVEAU", "FILIERE", "PARCOURS", "TYPEE") ENABLE;
  ALTER TABLE  "FORMATION" ADD CONSTRAINT "FORMATION_PK" PRIMARY KEY ("IDF") ENABLE;
  ALTER TABLE  "FORMATION" MODIFY ("IDCF" NOT NULL ENABLE);
  ALTER TABLE  "FORMATION" MODIFY ("TYPEE" NOT NULL ENABLE);
  ALTER TABLE  "FORMATION" MODIFY ("PARCOURS" NOT NULL ENABLE);
  ALTER TABLE  "FORMATION" MODIFY ("FILIERE" NOT NULL ENABLE);
  ALTER TABLE  "FORMATION" MODIFY ("NIVEAU" NOT NULL ENABLE);
  ALTER TABLE  "FORMATION" MODIFY ("GRADE" NOT NULL ENABLE);
  ALTER TABLE  "FORMATION" MODIFY ("IDF" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table INTERV_EC
--------------------------------------------------------

  ALTER TABLE  "INTERV_EC" ADD CONSTRAINT "UNIQUE_IDI_IDEC" UNIQUE ("IDI", "IDEC") ENABLE;
  ALTER TABLE  "INTERV_EC" MODIFY ("IDEC" NOT NULL ENABLE);
  ALTER TABLE  "INTERV_EC" MODIFY ("IDI" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table INTERVENANT
--------------------------------------------------------

  ALTER TABLE  "INTERVENANT" ADD CONSTRAINT "UNIQUE_IDI" UNIQUE ("NOM", "PRENOM", "TEL") ENABLE;
  ALTER TABLE  "INTERVENANT" ADD CONSTRAINT "PK_IDI" PRIMARY KEY ("IDI") ENABLE;
  ALTER TABLE  "INTERVENANT" MODIFY ("IDSTAT" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("TEL" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("PASSWD" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("MAIL" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("PRENOM" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("NOM" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("IDI" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" MODIFY ("ENSERVICE" NOT NULL ENABLE);
  ALTER TABLE  "INTERVENANT" ADD CONSTRAINT "CHECK_CONSTRAINT_ENSERVICE" CHECK (enService IN
      (
       'true',
       'fals'
      )
   ) ENABLE;
--------------------------------------------------------
--  Constraints for Table JOUR
--------------------------------------------------------

  ALTER TABLE  "JOUR" ADD CONSTRAINT "UNIQUE_JOUR_DATE" UNIQUE ("DATEE") ENABLE;
  ALTER TABLE  "JOUR" ADD CONSTRAINT "PK_IDJ" PRIMARY KEY ("IDJ") ENABLE;
  ALTER TABLE  "JOUR" ADD CHECK (feriee in ('true','fals')) ENABLE;
  ALTER TABLE  "JOUR" MODIFY ("FERIEE" NOT NULL ENABLE);
  ALTER TABLE  "JOUR" MODIFY ("DATEE" NOT NULL ENABLE);
  ALTER TABLE  "JOUR" MODIFY ("IDJ" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table PERIODE
--------------------------------------------------------

  ALTER TABLE  "PERIODE" ADD CONSTRAINT "PERIODE_UK1" UNIQUE ("IDP", "IDSEM") ENABLE;
  ALTER TABLE  "PERIODE" MODIFY ("FIN" NOT NULL ENABLE);
  ALTER TABLE  "PERIODE" MODIFY ("DEBUT" NOT NULL ENABLE);
  ALTER TABLE  "PERIODE" MODIFY ("IDSEM" NOT NULL ENABLE);
  ALTER TABLE  "PERIODE" MODIFY ("IDP" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table PROMOTION
--------------------------------------------------------

  ALTER TABLE  "PROMOTION" ADD CONSTRAINT "UNIQUE_PROM_FOR_ANNEE" UNIQUE ("IDF", "ANNEE") ENABLE;
  ALTER TABLE  "PROMOTION" ADD CONSTRAINT "PK_IDP" PRIMARY KEY ("IDP") ENABLE;
  ALTER TABLE  "PROMOTION" MODIFY ("ANNEE" NOT NULL ENABLE);
  ALTER TABLE  "PROMOTION" MODIFY ("IDF" NOT NULL ENABLE);
  ALTER TABLE  "PROMOTION" MODIFY ("IDP" NOT NULL ENABLE);
  ALTER TABLE  "PROMOTION" MODIFY ("NBRGRP" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table SALLE
--------------------------------------------------------

  ALTER TABLE  "SALLE" ADD CONSTRAINT "PK_IDS" PRIMARY KEY ("IDS") ENABLE;
  ALTER TABLE  "SALLE" MODIFY ("BATIMENT" NOT NULL ENABLE);
  ALTER TABLE  "SALLE" MODIFY ("NOM" NOT NULL ENABLE);
  ALTER TABLE  "SALLE" MODIFY ("NUM" NOT NULL ENABLE);
  ALTER TABLE  "SALLE" MODIFY ("IDS" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table SEMESTRE
--------------------------------------------------------

  ALTER TABLE  "SEMESTRE" ADD CONSTRAINT "UNIQUE_SEM_IDF" UNIQUE ("IDF", "NPERIODE") ENABLE;
  ALTER TABLE  "SEMESTRE" ADD CONSTRAINT "PK_IDSEM" PRIMARY KEY ("IDSEM") ENABLE;
  ALTER TABLE  "SEMESTRE" MODIFY ("NPERIODE" NOT NULL ENABLE);
  ALTER TABLE  "SEMESTRE" MODIFY ("IDF" NOT NULL ENABLE);
  ALTER TABLE  "SEMESTRE" MODIFY ("IDSEM" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table SESSIONS
--------------------------------------------------------

  ALTER TABLE  "SESSIONS" ADD CONSTRAINT "SESSIONS_PK" PRIMARY KEY ("KEYS") ENABLE;
  ALTER TABLE  "SESSIONS" MODIFY ("EXPIRE" NOT NULL ENABLE);
  ALTER TABLE  "SESSIONS" MODIFY ("KEYS" NOT NULL ENABLE);
  ALTER TABLE  "SESSIONS" MODIFY ("IDI" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table STATUT
--------------------------------------------------------

  ALTER TABLE  "STATUT" ADD CONSTRAINT "UNIQUE_STAT_NOM" UNIQUE ("NOM") ENABLE;
  ALTER TABLE  "STATUT" ADD CONSTRAINT "PK_IDSTAT" PRIMARY KEY ("IDSTAT") ENABLE;
  ALTER TABLE  "STATUT" MODIFY ("NBRHSUP" NOT NULL ENABLE);
  ALTER TABLE  "STATUT" MODIFY ("NBRH" NOT NULL ENABLE);
  ALTER TABLE  "STATUT" MODIFY ("NOM" NOT NULL ENABLE);
  ALTER TABLE  "STATUT" MODIFY ("IDSTAT" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table UE
--------------------------------------------------------

  ALTER TABLE  "UE" ADD CONSTRAINT "UNIQUE_UE" UNIQUE ("IDF", "NOM") ENABLE;
  ALTER TABLE  "UE" ADD CONSTRAINT "PK_UE" PRIMARY KEY ("IDUE") ENABLE;
  ALTER TABLE  "UE" MODIFY ("IDSEM" NOT NULL ENABLE);
  ALTER TABLE  "UE" MODIFY ("NOM" NOT NULL ENABLE);
  ALTER TABLE  "UE" MODIFY ("IDF" NOT NULL ENABLE);
  ALTER TABLE  "UE" MODIFY ("IDUE" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table AGENDA
--------------------------------------------------------

  ALTER TABLE  "AGENDA" ADD CONSTRAINT "FK_AG_IDEC" FOREIGN KEY ("IDEC") REFERENCES  "EC" ("IDEC") ENABLE;
  ALTER TABLE  "AGENDA" ADD CONSTRAINT "FK_AG_IDI" FOREIGN KEY ("IDI") REFERENCES  "INTERVENANT" ("IDI") ENABLE;
  ALTER TABLE  "AGENDA" ADD CONSTRAINT "FK_AG_IDJ" FOREIGN KEY ("IDJ") REFERENCES  "JOUR" ("IDJ") ENABLE;
  ALTER TABLE  "AGENDA" ADD CONSTRAINT "FK_AG_IDP" FOREIGN KEY ("IDP") REFERENCES  "PROMOTION" ("IDP") ENABLE;
  ALTER TABLE  "AGENDA" ADD CONSTRAINT "FK_AG_IDS" FOREIGN KEY ("IDS") REFERENCES  "SALLE" ("IDS") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table EC_UE
--------------------------------------------------------

  ALTER TABLE  "EC_UE" ADD CONSTRAINT "FK_IDEC" FOREIGN KEY ("IDEC") REFERENCES  "EC" ("IDEC") ENABLE;
  ALTER TABLE  "EC_UE" ADD CONSTRAINT "FK_IDF" FOREIGN KEY ("IDF") REFERENCES  "FORMATION" ("IDF") ENABLE;
  ALTER TABLE  "EC_UE" ADD CONSTRAINT "FK_IDUE" FOREIGN KEY ("IDUE") REFERENCES  "UE" ("IDUE") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table FICHEVOEUX
--------------------------------------------------------

  ALTER TABLE  "FICHEVOEUX" ADD CONSTRAINT "FK_IDI_INTR" FOREIGN KEY ("IDI") REFERENCES  "INTERVENANT" ("IDI") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table FORMATION
--------------------------------------------------------

  ALTER TABLE  "FORMATION" ADD CONSTRAINT "FORMATION_CONTRAT_FK1" FOREIGN KEY ("IDCF") REFERENCES  "CONTRAT" ("IDC") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table INTERV_EC
--------------------------------------------------------

  ALTER TABLE  "INTERV_EC" ADD CONSTRAINT "FK_IDEC_INTRV" FOREIGN KEY ("IDEC") REFERENCES  "EC" ("IDEC") ENABLE;
  ALTER TABLE  "INTERV_EC" ADD CONSTRAINT "FK_IDI_INTRV" FOREIGN KEY ("IDI") REFERENCES  "INTERVENANT" ("IDI") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table INTERVENANT
--------------------------------------------------------

  ALTER TABLE  "INTERVENANT" ADD CONSTRAINT "FK_IDSTAT" FOREIGN KEY ("IDSTAT") REFERENCES  "STATUT" ("IDSTAT") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table PERIODE
--------------------------------------------------------

  ALTER TABLE  "PERIODE" ADD CONSTRAINT "PERIODE_IDP" FOREIGN KEY ("IDP") REFERENCES  "PROMOTION" ("IDP") ENABLE;
  ALTER TABLE  "PERIODE" ADD CONSTRAINT "PERIODE_IDSEM" FOREIGN KEY ("IDSEM") REFERENCES  "SEMESTRE" ("IDSEM") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table PROMOTION
--------------------------------------------------------

  ALTER TABLE  "PROMOTION" ADD CONSTRAINT "FK_IDF_PR" FOREIGN KEY ("IDF") REFERENCES  "FORMATION" ("IDF") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table SEMESTRE
--------------------------------------------------------

  ALTER TABLE  "SEMESTRE" ADD CONSTRAINT "FK_SEM_IDF" FOREIGN KEY ("IDF") REFERENCES  "FORMATION" ("IDF") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table SESSIONS
--------------------------------------------------------

  ALTER TABLE  "SESSIONS" ADD CONSTRAINT "FK_SESSION_IDI" FOREIGN KEY ("IDI") REFERENCES  "INTERVENANT" ("IDI") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table UE
--------------------------------------------------------

  ALTER TABLE  "UE" ADD CONSTRAINT "FK_UE" FOREIGN KEY ("IDF") REFERENCES  "FORMATION" ("IDF") ENABLE;
  ALTER TABLE  "UE" ADD CONSTRAINT "FK_UE_IDSEM" FOREIGN KEY ("IDSEM") REFERENCES  "SEMESTRE" ("IDSEM") ENABLE;


--------------------------------------------------------
--  DDL for Trigger T_AGENDA_CONTRAINT_UNIQ
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_AGENDA_CONTRAINT_UNIQ" 
BEFORE INSERT
ON agenda
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	tot number :=0;
	n number:=0;
	msg varchar(1000) := '';
	annee1 date;
	annee2 date;
  annee date;
  laDate date;
  
  Deb agenda.debut%TYPE;
  finn agenda.fin%TYPE;
  duree number:=0;
  dureetotal number :=0; 
  
  minD agenda.debut%TYPE;
  heurD agenda.debut%TYPE;
  
  minF agenda.fin%TYPE;
  heurF agenda.fin%TYPE;

  Statut Varchar(50) := '';
  
  Nominterv Varchar(50) := '';
  Nomec Varchar(50) := '';
  Salle varchar(50) := '';
    

CURSOR Chjour IS SELECT debut,fin FROM agenda ag where ag.idj = :new.idj 
                                                  and ag.idi = :new.idi;

CURSOR Chsemaine IS SELECT ag.debut,ag.fin  from agenda ag, jour j1, jour j2
                          where ag.idi=:new.idi
                          and  j1.idj=:new.idj
                          and j2.datee between 
                                      (j1.datee-(to_char(j1.datee,'d'))) 
                                and(j1.datee+(5-(to_char(j1.datee,'d'))))
                          and j2.idj= ag.idj;
                          
CURSOR CnbrHEC IS 
SELECT sum(
((TRUNC(fin/100)*60)+MOD(fin,100)) - ((TRUNC(debut/100)*60)+MOD(debut,100)))  
from agenda
where idec = :new.idec and idp = :new.idp
and ((:new.typee = 'cm' and typee ='cm') 
or (:new.typee <>'cm' and typee <>'cm'));
                    
                    
CURSOR CnbrHCM IS 
Select Sum(
((Trunc(Ag.Fin/100)*60)+Mod(Ag.Fin,100)) - ((Trunc(ag.Debut/100)*60)+Mod(ag.Debut,100)))  
From Agenda Ag, Promotion P, Promotion P2
Where Typee='cm' and Ag.Idi = :New.Idi And P.Idp = :New.Idp 
and p2.annee = p.annee and ag.idp = p2.idp;


CURSOR CnbrHTDTP IS 
Select Sum(
((Trunc(Ag.Fin/100)*60)+Mod(Ag.Fin,100)) - ((Trunc(ag.Debut/100)*60)+Mod(ag.Debut,100)))  
From Agenda Ag, Promotion P, Promotion P2
Where Typee<>'cm' And Ag.Idi = :New.Idi And P.Idp = :New.Idp 
And P2.Annee = P.Annee And Ag.Idp = P2.Idp;


	contrainte_uniq_violated EXCEPTION;
  
Begin
	/*msg := msg || ' erreurs :'||CHR(10);*/
	
  if (:new.debut < 800 or :new.debut > 2000)then 
    msg := msg || '*) l heure de debut du creneau n est pas valide'||chr(10);
		tot := tot +1;
	end if;
  
  if (:new.fin < 829 or :new.fin > 2030)then 
    msg := msg || '*) l heure de fin du creneau n est pas valide'||chr(10);
		tot := tot +1;
	end if;
  
  If (:New.Debut >= :New.Fin)Then 
    Msg := Msg || '*) l heure de début du creneau ne peut pas etre inférieur à h fin'||Chr(10);
		Tot := Tot +1;
	end if;
  
  
	select count(idi) into n from agenda 
  where :new.idj = idj 
  and :new.idi = idi 
  and ( (:new.debut >= debut and :new.debut < fin) 
    or (:new.debut <= debut and :new.fin > debut)
    );
    
	if(n > 0) then
		msg := msg || '*) l enseignant a '||n||' creneau(s) qui est(sont) programmé(s) durant cet intervale de temps'||chr(10);
		tot := tot +n;
	end if;

	select count(idcr) into n 
        from agenda
        where :new.idj = idj
        and :new.ids = ids 
        and 
            ( (:new.debut >= debut and :new.debut < fin) 
          or (:new.debut <= debut and :new.fin > debut));
	if (n>0)then 
		msg := msg || '*) salle il existe '||n||' creneau(s) qui est(sont) programmé(s) pour cette salle durant cet intervale de temps'||chr(10);
		tot := tot +n;
	end if;
	
	
	select datee into annee 
              from jour 
              where :new.idj = jour.idj;
              
	select to_date('20-08-'||annee,'dd-mm-yyyy') into annee1 
              from promotion 
              where idp= :new.idp;
  annee2 := ADD_months(annee1,12);
  
  --msg := msg||chr(10)||annee1||chr(10)||annee||chr(10)||annee2||chr(10);
	if(annee1 > annee or annee2 < annee)then
    msg := msg || '*) le créneau à ajouter est en dehors de la periode de l annee scolaire de la promotion'||chr(10);
		tot := tot +1;
  end if;
  
	select count(idp) into n 
          from agenda 
          where :new.idj = idj 
          and :new.idp = idp 
          and :new.numG = numG 
          and
         ( (:new.debut >= debut and :new.debut < fin) 
        or (:new.debut <= debut and :new.fin > debut));
	if (n>0)then 
		msg := msg || '*) cette promotion a '||n||' creneau(s) qui est(sont) déja programmé(s) pour cet intervale de temps'||chr(10);
		tot := tot +n;
	end if;
  
  
  select count(ec_ue.idec) into n 
          from ec_ue,promotion  
          where promotion.idp = :new.idp  
          and promotion.idf = ec_ue.idf 
          and ec_ue.idec = :new.idec;
      
	if (n = 0 or n>1)then 
    msg := msg || '*) idEC n est pas valide, la formation de cette promotion ne contient pas ec mentioné '||chr(10);
		tot := tot +1;
	end if;
  
  
  select count(idj) into n from jour  where idj = :new.idj  
      and feriee='true';
      
	if (n <> 0)then 
    msg := msg || '*) le jour est fériée'||chr(10);
		tot := tot +1;
	end if;
        
  /*vérifier si l'enseignant est en service ou non !*/      
  select count(idi) into n from intervenant where idi = :new.idi and enservice='true';
  if (n <> 1)then 
    msg := msg || '*) l enseignant n est plus en service !'||chr(10);
		tot := tot +1;
	end if;
  
  /*Cette requete vérifie l'existance de la fiche de voeux de l intervenant dans
  le cas ou elle n existe pas ça veut dire qu il n est pas diponible.
  */
  
             
  select count(f.idi) into n from fichevoeux f, promotion p, jour j 
      where f.idi = :new.idi 
      and j.idj = :new.idj
      and f.idjour = to_char(j.datee,'d') 
      and f.annee = p.annee 
      and p.idp = :new.idp
      and f.poids > 0
      and ( (  f.periode = 'S1' and ( extract(MONTH FROM j.datee) > 8 
                                    or extract(MONTH FROM j.datee) = 1) )  
         or (f.periode = 'S2' and  extract(MONTH FROM  j.datee) > 1 
                                and extract(MONTH FROM  j.datee) < 9  ) )
      and ((:new.debut < 1200 and (f.ncreneau =1 or f.ncreneau =3)) or 
           (:new.debut > 1200 and (f.ncreneau =2 or f.ncreneau =3)) );         
            
	if (n = 0)then
    msg := msg || '*) La fiche de voeux de l enseignant indique qu il n est pas disponible'||chr(10);
		tot := tot +1;
	end if;

/*Cette requete vérifie le nombre d heure de travail de l enseignant par jour (<=8h) !! il faut utiliser les curseurs !!*/
/*
OPEN Chjour;

dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;

LOOP
FETCH Chjour INTO deb, finn;
EXIT WHEN Chjour%NOTFOUND;
minD := MOD(deb, 100);
heurD := TRUNC (deb / 100);
minF := MOD(finn,100);
heurF := TRUNC(finn/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;
END LOOP;

if (dureetotal > 480)then
    msg := msg || '*) l enseignant ne peut pas travailler plus de 8h par jour'||chr(10);
		tot := tot +1;
	end if;

CLOSE Chjour;
*/


/*Cette requete vérifie le nombre d heure de travail de l enseignant par semaine (<=36h)*/

/*
OPEN Chsemaine;
dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;

LOOP
FETCH Chsemaine INTO deb, finn;
EXIT WHEN Chsemaine%NOTFOUND;
minD := MOD(deb, 100);
heurD := TRUNC (deb / 100);
minF := MOD(finn,100);
heurF := TRUNC(finn/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;
END LOOP;


if (dureetotal > 2160)then
    msg := msg || '*) l enseignant ne peut pas travailler plus de 36h par semaine'||chr(10);
		tot := tot +1;
end if;

CLOSE Chsemaine;
*/

/*vérification qu'on dépasse pas la quantité autorisé en nbr heure liée à la formation*/

OPEN CnbrHEC;
dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;

LOOP
FETCH CnbrHEC INTO duree;
EXIT WHEN CnbrHEC%NOTFOUND;
dureetotal := dureetotal +duree;
END LOOP;


if(:new.typee='cm')then
  select e.nbrcm into n from ec_ue e,promotion p
    where e.idec = :new.idec 
    and e.idf= p.idf 
    and p.idp = :new.idp 
    and p.idf=e.idf;
else
    select e.nbrtd into n from ec_ue e,promotion p
    where e.idec = :new.idec 
    and e.idf= p.idf 
    and p.idp = :new.idp 
    and p.idf=e.idf;
end if;  

If (Dureetotal > (N*60))Then
    msg := msg || '*) Cet Ec dépasse le nombre d heure autorisé qui est définit sur 
    la plaquette de la formation. Le dépassement est de '||(dureetotal - (n*60))||' minutes '||chr(10);
		tot := tot +1;
end if;

CLOSE CnbrHEC;
  
  
/*vérification que l'enseignant ne dépasse pas la quantité heure définit selon son statut*/

OPEN CnbrHCM;
dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
Duree := ((Heurf * 60) + Minf)-((Heurd * 60)+Mind);
if (:new.typee='cm') then Duree := duree *1.5; end if;
dureetotal := dureetotal +duree;

LOOP
FETCH CnbrHCM INTO duree;
Exit When Cnbrhcm%Notfound;
Dureetotal := Dureetotal +(Duree*1.5);
END LOOP;  
  
CLOSE CnbrHCM;


OPEN CnbrHTDTP;
  
LOOP
FETCH CnbrHTDTP INTO duree;
EXIT WHEN CnbrHTDTP%NOTFOUND;
Dureetotal := Dureetotal +Duree;
End Loop;

Select (S.Nbrh+S.Nbrhsup) Into N  
From Statut S, Intervenant I Where I.Idi = :New.Idi 
and i.idstat = s.idstat;


If (Dureetotal > (N*60) )Then
  
    Select S.Nom Into Statut From Intervenant I, Statut S Where I.Idi = :New.Idi And I.Idstat = S.Idstat; 
    If(Statut = 'Professeur' or Statut = 'Maitre de Conferences') Then
        /* Mettre un Warning !!!! */
        Statut := '';
    Else
      Msg := Msg || '*) Cet intervenant dépasse le nombre d heure définit sur 
      son statut. Le dépassement est de '||(dureetotal - (n*60))||' minutes '||chr(10);
      Tot := Tot +1;
    end if;  
end if;

CLOSE CnbrHTDTP; 
  

  if(tot > 0 ) then
	RAISE contrainte_uniq_violated;
  end if;
    
  
  EXCEPTION
	When Contrainte_Uniq_Violated Then
  Select Nom Into Nomec From Ec Where Idec=:New.Idec;
  Select Nom Into Salle From Salle Where Ids = :New.Ids;
  Select Prenom||' '||Nom Into Nominterv From Intervenant Where Idi = :New.Idi;
	Msg := Chr(10)||tot||' Erreurs :'||' Ajout créneau le '||Annee||' de '||Trunc(:New.Debut/100)||':'||
        Mod(:New.Debut, 100)||'h à '||Trunc(:New.Fin/100)||':'||
        Mod(:New.Fin, 100)||'h '||Upper(:New.Typee)||' '||Nomec||' Ens: '||
        Nominterv||' Salle: '||Salle||chr(10)||msg;
	Raise_application_error(-20324,msg);
	WHEN no_data_found THEN
	n :=0 ;
END;

/

--------------------------------------------------------
--  DDL for Trigger T_AGENDA_DELETE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_AGENDA_DELETE" 
BEFORE delete
ON agenda
FOR EACH ROW
DECLARE 
	n Date;
	can_not_delete_cr EXCEPTION;
Begin
	select j.datee into n from  jour j where j.idj = :old.idj;
  if(n <  SYSDATE ) then
	Raise Can_Not_Delete_Cr;
  end if;
  EXCEPTION
	When Can_Not_Delete_Cr Then
	Raise_application_error(-20324,'1 erreur :'||CHR(10)||'Désolé vous ne pouvez pas supprimer un créneau qui a déjà été éffectuer le '||n||' .');
END;
/
--------------------------------------------------------
--  DDL for Trigger T_AGENDA_IDCR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_AGENDA_IDCR" 
BEFORE INSERT
ON agenda
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idcr is null) then
  SELECT S_agenda.nextval
  INTO :new.idcr
  FROM dual;
  end if;
END;
/

--insert into agenda values(null,1,1,4,1,5,0800,1000,1,'td,'true','true');

--------------------------------------------------------
--  DDL for Trigger T_CONTRAT_IDC
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_CONTRAT_IDC" 
BEFORE INSERT
ON contrat
REFERENCING NEW AS NEW
FOR EACH ROW
Declare 
annee_not_valide EXCEPTION;
BEGIN
  if(:new.idc is null) then
  SELECT S_contrat.nextval
  INTO :new.idc
  FROM dual;
  end if;
  
  if ((:new.anneeex < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (:new.anneeex > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    RAISE annee_not_valide;
  end if;
  
  EXCEPTION
	WHEN annee_not_valide THEN
	Raise_application_error(-20324,'la date d expiration du contrat n est pas valide');
END;
/
--------------------------------------------------------
--  DDL for Trigger T_EC_IDEC
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_EC_IDEC" 
BEFORE INSERT
ON ec
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idec is null) then
  SELECT S_ec.nextval
  INTO :new.idec
  FROM dual;
  end if;
END;
/
--------------------------------------------------------
--  DDL for Trigger T_FORMATION_IDF
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_FORMATION_IDF" 
BEFORE INSERT
ON formation
REFERENCING NEW AS NEW
FOR EACH ROW
declare 
annee number;
idc_not_valide exception;
BEGIN
  if(:new.idf is null) then
  SELECT S_formation.nextval
  INTO :new.idf
  FROM dual;
  end if;
 
  select anneeex into annee from contrat where contrat.idc = :new.idcf;
  if ((annee < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (annee > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    RAISE idc_not_valide;
  end if;
  
  EXCEPTION
	WHEN idc_not_valide THEN
	Raise_application_error(-20324,'erreur le contrat attribué n est pas valide');
END;
/

--------------------------------------------------------
--  DDL for Trigger T_INTERVENANT_IDI
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_INTERVENANT_IDI" 
BEFORE INSERT
ON intervenant
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idi is null) then
  SELECT S_intervenant.nextval
  INTO :new.idi
  FROM dual;
  end if;
END;
/

--------------------------------------------------------
--  DDL for Trigger T_JOUR_IDJ
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_JOUR_IDJ" 
BEFORE INSERT
ON jour
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idj is null) then
  SELECT S_jour.nextval
  INTO :new.idj
  FROM dual;
  end if;
END;
/

--------------------------------------------------------
--  DDL for Trigger T_JOUR_UPDATE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_JOUR_UPDATE" 
BEFORE update
ON jour
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	n number;
	jour_non_modifiable EXCEPTION;
	
BEGIN
	select count(idcr) into n from agenda where :new.idj = idj;
  if(n > 0 or SYSDATE > :new.datee) then
	RAISE jour_non_modifiable;
  end if;
  EXCEPTION
	WHEN jour_non_modifiable THEN
	Raise_application_error(-20324,'Désolé vous ne pouvez pas modifier le type de jour car il y a déja '||n||' creneaux qui sont programés');
END;
/

--------------------------------------------------------
--  DDL for Trigger T_PROMO_IDP
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_PROMO_IDP" 
BEFORE INSERT
ON promotion
REFERENCING NEW AS NEW
FOR EACH ROW
declare
	annee number;
	idf_not_valide exception;
BEGIN
  if(:new.idp is null) then
  SELECT S_promotion.nextval
  INTO :new.idp
  FROM dual;
  end if;
  
  if ((:new.annee < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (:new.annee > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    Raise Idf_Not_Valide;
  end if;
  
  select anneeex into annee from contrat ,formation where idf= :new.idf and idcf = idc;
  if ((annee < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (annee > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    RAISE idf_not_valide;
  end if;
  
  EXCEPTION
	WHEN idf_not_valide THEN
	Raise_application_error(-20324,'erreur le contrat de la formation n est plus valable');
  
END;
/

--------------------------------------------------------
--  DDL for Trigger T_SALLE_IDS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_SALLE_IDS" 
BEFORE INSERT
ON salle
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.ids is null) then
  SELECT S_salle.nextval
  INTO :new.ids
  FROM dual;
  end if;
END;
/

--------------------------------------------------------
--  DDL for Trigger T_SEMESTRE_IDSEM
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_SEMESTRE_IDSEM" 
BEFORE INSERT
ON semestre
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idsem is null) then
  SELECT S_semestre.nextval
  INTO :new.idsem
  FROM dual;
  end if;
END;
/

--------------------------------------------------------
--  DDL for Trigger T_SEMESTRE_NPERIODE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_SEMESTRE_NPERIODE" 
BEFORE insert
ON semestre
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	nperiode_non_null EXCEPTION;
	
BEGIN
	
  if(:new.nperiode <= 0) then
	RAISE nperiode_non_null;
  end if;
  EXCEPTION
	WHEN nperiode_non_null THEN
	Raise_application_error(-20324,'nombre de periode non null');
END;
/

--------------------------------------------------------
--  DDL for Trigger T_STAT_IDSTAT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_STAT_IDSTAT" 
BEFORE INSERT
ON STATUT
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idstat is null) then
  SELECT S_status.nextval
  INTO :new.idstat
  FROM dual;
  End If;
END;
/

--------------------------------------------------------
--  DDL for Trigger T_UE_IDUE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER  "T_UE_IDUE" 
BEFORE INSERT
ON ue
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idue is null) then
  SELECT S_ue.nextval
  INTO :new.idue
  FROM dual;
  end if;
END;
/


ALTER TRIGGER  "T_AGENDA_CONTRAINT_UNIQ" ENABLE;
ALTER TRIGGER  "T_AGENDA_DELETE" ENABLE;
ALTER TRIGGER  "T_AGENDA_IDCR" ENABLE;
ALTER TRIGGER  "T_CONTRAT_IDC" ENABLE;
ALTER TRIGGER  "T_EC_IDEC" ENABLE;
ALTER TRIGGER  "T_FORMATION_IDF" ENABLE;
ALTER TRIGGER  "T_INTERVENANT_IDI" ENABLE;
ALTER TRIGGER  "T_JOUR_IDJ" ENABLE;
ALTER TRIGGER  "T_JOUR_UPDATE" ENABLE;
ALTER TRIGGER  "T_PROMO_IDP" ENABLE;
ALTER TRIGGER  "T_SALLE_IDS" ENABLE;
ALTER TRIGGER  "T_SEMESTRE_IDSEM" ENABLE;
ALTER TRIGGER  "T_SEMESTRE_NPERIODE" ENABLE;
ALTER TRIGGER  "T_STAT_IDSTAT" ENABLE;
ALTER TRIGGER  "T_UE_IDUE" ENABLE;
